﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class DBhelper
    {
        //将UI层的App.Config引入,完成数据库连接
        private static string str = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        public static SqlConnection conn = new SqlConnection(str);

        DBhelper()
        {
            //conn = new SqlConnection();
        }

        public static DataSet GetDataSet(string sql)
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn); //定义用户填充数据集DataSet的SQL数据适配器
            DataSet ds = new DataSet(); //定义数据集容器
            sda.Fill(ds);//使用数据适配器的返回结果
            return ds;
        }

        public static DataSet QueryData(SqlCommand cmd)
        {
            if (string.IsNullOrEmpty(cmd.CommandText))
                return null;

            conn = new SqlConnection(str); //创建数据库连接
            //判断数据库是否连接成功
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            cmd.Connection = conn;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        //读库操作，提供对查询结果的影响
        public static SqlDataReader ExecueReader(SqlCommand cmd)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            cmd.Connection = conn; //连接数据库
            SqlDataReader reader;
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        //执行SQL查询 返回一项数据结果（值）
        public static object GetScalar(SqlCommand cmd)
        {
            cmd.Connection = conn; //连接数据库
            object obj = new SqlCommand();//建立object对象，注意实例化数据类型
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                obj = cmd.ExecuteScalar();//调用SqlCommand对象的ExecuteScalar方法执行sql语句
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close(); //关闭数据库连接
            }
            return obj;
        }
        //实现数据库的增删查改
        public static int ExecuteSQL(SqlCommand cmd)
        {
            conn = new SqlConnection(str); //连接数据库
            cmd.Connection = conn;
            if (conn.State == ConnectionState.Closed)
                conn.Open(); //打开数据库
            int num = 0;
            try
            {
                num = cmd.ExecuteNonQuery();//调用SqlCommand的ExecuteNonQuery方法执行SQL
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close(); //关闭数据库连接
            }
            return num;
        }
    }
}
